﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entidad;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Dao
{
    public class AsignarDocenteAsignaturaDao
    {
        private static SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AcademicoConnectionString"].ConnectionString);
        public static SqlCommand cmd;
        public static SqlTransaction tran;

        public AsignarDocenteAsignaturaDao()
        {
            tran = null;
        }
        internal static void InsertarCurso(Asignatura asiD, SqlConnection cn, SqlTransaction tran)
        {
            string sql = "insert into Curso (numAula,capacidad) values (@NumAula,@Capacidad); Select @@Identity as codCurso";
            cmd = new SqlCommand(sql, cn, tran);
            cmd.Parameters.Add("@NumAula", SqlDbType.Int).Value = asiD.numAula;
            cmd.Parameters.Add("@Capacidad", SqlDbType.Int).Value = asiD.capacidad;
            asiD.codCurso = Convert.ToInt32(cmd.ExecuteScalar());
        }
        internal static void InsertarAsignatura(int legajo,Asignatura asiD, SqlConnection cn, SqlTransaction tran)
        {
            string sql = "insert into Asignatura (legDocente,codMateria,codCurso,turno) values (@LegDocente,@CodMateria,@CodCurso,@Turno); Select @@Identity as codAsignatura";
            cmd = new SqlCommand(sql, cn, tran);
            cmd.Parameters.Add("@LegDocente", SqlDbType.Int).Value=legajo;
            cmd.Parameters.Add("@CodMateria", SqlDbType.Int).Value= asiD.codMateria;
            cmd.Parameters.Add("@CodCurso", SqlDbType.Int).Value =asiD.codCurso;
            cmd.Parameters.Add("@Turno", SqlDbType.NChar, 10).Value=asiD.turno;
            asiD.codAsignatura = Convert.ToInt32(cmd.ExecuteScalar());
        }
        internal static void ActualizarCargaHoraria(int legajo,Asignatura asiD, SqlConnection cn, SqlTransaction tran)
        {

            string sql = @"update Docente set cargaHoraria=cargaHoraria+m.cantHoras from Asignatura a inner join Materia m on a.codMateria=m.codMateria where a.legDocente=@LegDocente and a.legDocente=Docente.legajo and m.codMateria=@CodMateria";
            cmd = new SqlCommand(sql, cn, tran);
            cmd.Parameters.Add("@LegDocente", SqlDbType.Int).Value = legajo;
            cmd.Parameters.Add("@CodMateria", SqlDbType.Int).Value = asiD.codMateria;
            cmd.ExecuteNonQuery();
        }
        public static void Insertar(List<Asignatura> listAsiD, int legajo)
        {

            try
            {
                cn.Open();
                tran = cn.BeginTransaction();

                foreach (Asignatura asiD in listAsiD.ToList())
                {
                    InsertarCurso(asiD, cn, tran);
                    InsertarAsignatura(legajo,asiD, cn, tran);
                    ActualizarCargaHoraria(legajo,asiD, cn, tran);
                }

                tran.Commit();
                cn.Close();
            }
            catch (SqlException ex)
            {
                if (cn.State == ConnectionState.Open)
                {
                    tran.Rollback();
                    cn.Close();
                }
                throw new ApplicationException("Error al insertar la Asignatura para el docente. \n Problema:" + ex.Message);
            }
        }

        public static List<Docente> CargarDDLDocente()
        {
            string sql = "Select legajo, apellido + ', ' + nombre as apenom from Docente ORDER BY apellido";
            List<Docente> listDoc = new List<Docente>();
            try
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Docente doc = new Docente();
                    doc.apenom=dr["apenom"].ToString();
                    doc.legDocente= (int)dr["legajo"];
                    listDoc.Add(doc);
                }
                dr.Close();
                cn.Close();
            }
            catch (SqlException ex)
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                throw new ApplicationException("Error al obtener los Docentes. \n Problema:" + ex.Message);
            }
            return listDoc;
        }

        public static bool ValidarAsignacion(int idMateria, int curso, string turno)
        {
            string sql = "select * from Asignatura inner join Curso on Asignatura.codCurso=Curso.codCurso where codMateria=@CodMateria and numAula=@NumAula and turno=@Turno";
        cmd = new SqlCommand(sql, cn);
        cmd.Parameters.Add("@CodMateria", SqlDbType.Int).Value =  idMateria;
        cmd.Parameters.Add("@NumAula", SqlDbType.Int).Value = curso;
        cmd.Parameters.Add("@Turno", SqlDbType.NChar,10).Value = turno;
        bool valido = true;
        try
        {
            cn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                valido = false;
            }
            dr.Close();
            cn.Close();
        }
        catch (SqlException ex)
        {
            if (cn.State == ConnectionState.Open)
                cn.Close();
            throw new ApplicationException("Error al validar Asignatura. \n Problema:" + ex.Message);
        }
        return valido;
        }
    }

}
